import sqlite3 as sql
import os

SEPERATOR = '/' # All paths will be converted to use forward slash as the seperator

if os.sep != SEPERATOR:
    def normalize(path):
        return path.replace(os.sep, SEPERATOR)
    def denormalize(path):
        return path.replace(SEPERATOR, os.sep)
else:
    def normalize(path):
        return path
    def denormalize(path):
        return path

def sql__path_count(path):
    '''Returns the number of components in the given path'''
    return sql__path_dir(path).count(SEPERATOR)
def sql__path_join(*args):
    '''Joins path components together'''
    if len(args) == 0:
        return None
    return normalize(os.path.join(*args))
def sql__path_root(path):
    '''Returns the root component of @path'''
    root, remainder = os.path.splitunc(path)
    if root == '':
        root, remainder = os.path.splitdrive(path)
    return root
def sql__path_dir(path):
    '''Returns the directory component of @path'''
    root, remainder = os.path.splitunc(path)
    if root == '':
        root, remainder = os.path.splitdrive(path)
    return remainder


def connect(filename):
    conn = sql.connect(filename)

    conn.row_factory  = sql.Row
    conn.text_factory = str

    conn.create_function('path_count', 1, sql__path_count)
    conn.create_function('path_join', -1, sql__path_join)
    conn.create_function('path_root', 1, sql__path_root)
    conn.create_function('path_dir', 1, sql__path_dir)

    return conn

if __name__ == '__main__':
    db = connect(':memory:')
    db.execute("CREATE TABLE test (a,b,c)")
    db.execute("INSERT INTO test values (?,?,?)", ('a', 'b', 'c'))
    db.execute("INSERT INTO test values (?,?,?)", ('1', '2', '3'))
    db.execute("INSERT INTO test values (?,?,?)", ('x', 'y', 'z'))
    db.execute("INSERT INTO test values (?,?,?)", ('//computer/share/folder/subfolder', 'c:/folder/subfolder', '/one/two'))

    rows = db.execute("""
        SELECT
            *,
            path_count(a) as count_a,
            path_count(b) as count_b,
            path_count(c) as count_c,
            path_join(a,b,c) as join_abc,
            path_join(a,b) as join_ab,
            path_join(a,c) as join_ac,
            path_root(a) as root_a,
            path_root(b) as root_b,
            path_root(c) as root_c,
            path_dir(a) as dir_a,
            path_dir(b) as dir_b,
            path_dir(c) as dir_c
        FROM
            test
    """)

    def pr(**kwargs):
        k = list(kwargs.keys())
        k.sort()
        print k

    for row in rows:
        print ['%s:%r' % (k, row[k]) for k in row.keys()]
        pr(**row)


    maxrows = 10
    import time

    os.remove('c:/temp/test.db')
    db = connect('c:/temp/test.db')
    db.execute('CREATE TABLE test (i PRIMARY KEY,j)')
    with db:
        for i in range(maxrows):
            db.execute('INSERT INTO test VALUES (?,?)', (i, 'a'))

    start = time.clock()
    with db:
        for i in range(maxrows):
            db.execute("UPDATE test SET j='b' WHERE i=?", (i,))
    end   = time.clock()
    print 'one transaction                         : %f' % (end - start)

    start = time.clock()
    for i in range(maxrows):
        with db:
            db.execute("UPDATE test SET j='b' WHERE i=?", (i,))
    end   = time.clock()
    print 'seperate transactions                   : %f' % (end - start)

    start = time.clock()
    with db:
        for i in range(maxrows):
            db.execute("UPDATE test SET j='b' WHERE i=?", (i,))
            db.execute("SELECT 1 FROM test LIMIT 1")
    end   = time.clock()
    print 'one transaction with mixed update/select: %f' % (end - start)

